﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CodeGeneratorHelper.Entities;


namespace CodeGeneratorHelper.DAL
{
    public class DBHelper
    {
        public static string TargetConnectionStr = "";
        public static string SourceConnectionStr = "";
        public static void ExecuteCustomizedQuery(string sQuery)
        {
            string strDbConn = UserSettings.TargetConnectionStr;
            SqlConnection conn = new SqlConnection(strDbConn);
            try
            {
                SqlCommand command = conn.CreateCommand();
                command.CommandText = sQuery;
                conn.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }

        }

        public static DataSet ExecuteDatasetQuery(string sQuery)
        {
            string strDbConn = UserSettings.TargetConnectionStr;

            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(strDbConn);
            try
            {
                //SqlCommand command = conn.CreateCommand();
                //command.CommandText = sQuery;
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sQuery, conn);
                //command.ExecuteNonQuery();
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }
        public static DataSet ExecuteSourceDatasetQuery(string sQuery)
        {
            string strDbConn = SourceConnectionStr;

            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(strDbConn);
            try
            {
                //SqlCommand command = conn.CreateCommand();
                //command.CommandText = sQuery;
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sQuery, conn);
                //command.ExecuteNonQuery();
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }

        public static string GetDBString(string sInput)
        {
            if (String.IsNullOrEmpty(sInput))
            {
                return sInput;
            }
            else
            {
                return sInput.Replace("'", "''");
            }

        }
        public static string GetIntValueOfBool(string sInput)
        {
            if (String.IsNullOrEmpty(sInput))
            {
                return null;
            }
            else
            {
                if (sInput.ToUpper().Equals("TRUE"))
                    return "1";
                else
                    return "0";
            }

        }
        public static string GetStringValue(long? iInput)
        {
            if (iInput.HasValue)
            {
                return iInput.ToString();
            }
            else
            {
                return "null";
            }

        }
        public static string GetStringValue(string iInput)
        {
            if (String.IsNullOrEmpty(iInput))
            {
                return "NULL";
            }
            return iInput;

        }
        public static bool IsDataSetEmpty(DataSet ds)
        {
            if (ds == null || (ds.Tables != null && ds.Tables.Count == 0) || ds.Tables[0].Rows.Count == 0)
                return true;

            return false;
        }
        public static DateTime GetCurrentDBTime()
        {
            DateTime dtNow = DateTime.Now;
            DataSet dsTime = DBHelper.ExecuteDatasetQuery("Select GETDATE()");
            if (!DBHelper.IsDataSetEmpty(dsTime))
            {
                dtNow = Convert.ToDateTime(dsTime.Tables[0].Rows[0][0]);
            }
            return dtNow;
        }
        public static StringBuilder GetCommaSeparatedString(List<string> items)
        {
            StringBuilder sbApps = new StringBuilder();
            for (int i = 0; i < items.Count; i++)
            {
                if (i == 0)
                {
                    sbApps.Append(items[i]);
                }
                else
                {
                    sbApps.Append(",");
                    sbApps.Append(items[i]);
                }
            }
            return sbApps;
        }
        public static List<ColumnInfo> GetColumnsInfo(string tableName)
        {
            SqlConnection conn = new SqlConnection(UserSettings.TargetConnectionStr);
            List<ColumnInfo> lstColumnInfo = new List<ColumnInfo>();
            try
            {

                SqlCommand command = conn.CreateCommand();
                command.CommandText = "Select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = '"+tableName+"'";
                conn.Open();

                SqlDataReader objReader = command.ExecuteReader();


                while (objReader.Read())
                {
                    ColumnInfo oColumnInfo = new ColumnInfo();

                    oColumnInfo.Name = objReader["COLUMN_NAME"] != DBNull.Value ? objReader["COLUMN_NAME"].ToString() : "";
                    oColumnInfo.Type = objReader["DATA_TYPE"] != DBNull.Value ? GetDBType(objReader["DATA_TYPE"].ToString()) : DBColumnType.VarChar;
                    oColumnInfo.Length = objReader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? Convert.ToInt32(objReader["CHARACTER_MAXIMUM_LENGTH"]) : 0;
                    oColumnInfo.Numeric_Precision = objReader["NUMERIC_PRECISION"] != DBNull.Value ? Convert.ToInt32(objReader["NUMERIC_PRECISION"]) : 0;
                    oColumnInfo.Numeric_Scale = objReader["NUMERIC_SCALE"] != DBNull.Value ? Convert.ToInt32(objReader["NUMERIC_SCALE"]) : 0;
                    lstColumnInfo.Add(oColumnInfo);
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return lstColumnInfo;

        }

        private static DBColumnType GetDBType(string dbType)
        {
            DBColumnType type = DBColumnType.VarChar;
            switch (dbType)
            {
                case "numeric":
                    type = DBColumnType.Numeric;
                    break;
                case "varchar":
                    type = DBColumnType.VarChar;
                    break;
                case "datetime":
                    type = DBColumnType.DateTime;
                    break;
                case "date":
                    type = DBColumnType.Date;
                    break;
                case "int":
                    type = DBColumnType.Int;
                    break;
                case "decimal":
                    type = DBColumnType.Decimal;
                    break;
                case "bit":
                    type = DBColumnType.Boolean;
                    break;
                case "uniqueidentifier":
                    type = DBColumnType.UniqIdentifier;
                    break;
                
            }
            return type;
        }
       
    }
}
